﻿using Aspose.Cells;
using DataImporter.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DataImporter
{
    /// <summary>
    /// 帮助扩展类
    /// </summary>
    public static class HelperExtentions
    {
        private static readonly string[] letters = new string[] { };
        private static readonly PropertyInfo[] excelProperties = typeof(Excel).GetProperties();
        private static readonly Dictionary<string, string> symbols = new Dictionary<string, string>
        {
            { "≤", "<=" },
            { "£", "v" },
             {"＜", "<" },
             {"≦", "<="},
             {"≥", ">="},
             {"＞", ">"},
             {"—", "-"},
             {"〇", "v"},
             {"~", "-"}
        };    //符号

        public static DataTable GetDataTable(this string path, string sheetName)
        {
            Workbook workbook = new Workbook(path);
            Cells cells = workbook.Worksheets.Find(c => c.Name.Contains(sheetName)).Cells;
            DataTable dt = new DataTable();
            bool d = true;//防止表头重复加载
            for (int i = 0; i < cells.MaxDataRow + 1; i++)
            {
                DataRow row = dt.NewRow();
                for (int j = 0; j < cells.MaxDataColumn + 1; j++)
                {
                    if (d)
                        dt.Columns.Add(cells[0, j].StringValue.Trim());

                    row[j] = cells[i + 1, j].StringValue.Trim();
                }
                dt.Rows.Add(row);
                d = false;
            }
            return dt;
        }

        /// <summary>
        /// 是否为Null或为空
        /// </summary>
        /// <param name="source">要验证的数据</param>
        /// <returns></returns>
        public static bool IsNullOrEmpty(this object source)
        {
            if (null == source)
                return true;
            return source is string && string.IsNullOrWhiteSpace(source.ToString());
        }

        /// <summary>
        /// 是否为Null或为空
        /// </summary>
        /// <param name="source">要验证的数据</param>
        /// <returns></returns>
        public static string IsNullOrEmptyDefaultValue(this object source, string defaultValue = "")
        {
            if (source.IsNullOrEmpty())
                return defaultValue;
            return source.ToString();
        }

        /// <summary>
        /// 转换null
        /// </summary>
        /// <param name="source">对象</param>
        /// <returns>转换后的字符串</returns>
        public static string ConvertNull(this object source)
        {
            if (source.IsNullOrEmpty())
                return null;
            if ("null".Equals(source.ToString(), StringComparison.CurrentCultureIgnoreCase))
                return null;
            return source.ToString();
        }

        /// <summary>
        /// 转换为Excel行
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static Excel ToExcelRow(this string source)
        {
            if (source.IsNullOrEmpty())
                return null;
            var items = source.Split('\t');
            var excel = new Excel();
            for (int i = 0; i < items.Length; i++)
            {
                var letter = ((char)(65 + i)).ToString();
                if (excelProperties.FirstOrDefault(c => c.Name == letter) is PropertyInfo property)
                    property.SetValue(excel, items[i].Trim());
            }
            return excel;
        }

        /// <summary>
        /// 转换为Excel列表
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static List<Excel> ToExcelList(this string source)
        {
            var lst = new List<Excel>();
            if (source.IsNullOrEmpty())
                return lst;
            var lines = source.Split('\n');
            foreach (var line in lines)
                if (line.ToExcelRow() is Excel excel)
                    lst.Add(excel);
            return lst;
        }

        public static List<Excel> ToExcelList(this string path, string sheetName)
        {
            var workbook = new Workbook(path);
            Cells cells = null;
            foreach (var sheet in workbook.Worksheets)
                if (sheet.Name == sheetName)
                {
                    cells = sheet.Cells;
                    break;
                }
            var dt = new DataTable();
            var lst = new List<Excel>();
            bool d = true;//防止表头重复加载
            for (int i = 0; i < cells.MaxDataRow + 1; i++)
            {
                var row = dt.NewRow();
                for (var j = 0; j < cells.MaxDataColumn + 1; j++)
                {
                    if (d)
                        dt.Columns.Add(cells[0, j].StringValue.Trim());
                    row[j] = cells[i + 1, j].StringValue.Trim();
                }
                dt.Rows.Add(row);
                d = false;
            }
            foreach (DataRow row in dt.Rows)
            {
                var excel = new Excel();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var letter = ((char)(65 + j)).ToString();
                    if (excelProperties.FirstOrDefault(c => c.Name == letter) is PropertyInfo property)
                        property.SetValue(excel, row[j].ToString());
                }
                lst.Add(excel);
            }
            return lst;
        }

        /// <summary>
        /// 保存到Excel文件
        /// </summary>
        /// <param name="dataTable">要保存的表</param>
        /// <param name="filePath">保存路径</param>
        public static void SaveToExcel(this DataTable dataTable, string filePath, params int[] mergeColumnIndex)
        {
            var book = new Workbook();
            var sheet = book.Worksheets[0];
            var cells = sheet.Cells;
            var Colnum = dataTable.Columns.Count;//表格列数
            var Rownum = dataTable.Rows.Count;//表格行数
            for (int i = 0; i < Colnum; i++)
                cells[0, i].PutValue(dataTable.Columns[i].ColumnName);
            //生成数据行
            for (int i = 0; i < Rownum; i++)
                for (int k = 0; k < Colnum; k++)
                {
                    var content = dataTable.Rows[i][k].ToString();
                    if (mergeColumnIndex?.Contains(k) ?? false)
                    {
                        if (i > 0)
                        {
                            var prev = dataTable.Rows[i - 1][k].ToString();
                            if (content == prev)
                                continue;
                        }
                        var j = i + 1;
                        for (; j < dataTable.Rows.Count; j++)
                        {
                            var current = dataTable.Rows[j][k].ToString();
                            if (current != content)
                                break;
                        }
                        cells.Merge(1 + i, k, j - i, 1);
                    }
                    cells[1 + i, k].PutValue(content);

                }
            book.Save(filePath);
        }

        /// <summary>
        /// 替换条件
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static string ReplaceCondition(this string source)
        {
            if (source.IsNullOrEmpty())
                return source;
            source = source.Trim();
            foreach (var item in symbols)
                source = source.Replace(item.Key, item.Value);
            return source;
        }

        /// <summary>
        /// 替换空格
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static string ReplaceSpace(this string source)
        {
            if (source.IsNullOrEmpty())
                return source;
            return Regex.Replace(source, "\\s", "");
        }

        /// <summary>
        /// 解析条件
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static string AnalizeCondition(this string source)
        {
            if (source.IsNullOrEmpty())
                return null;
            source = source.ReplaceCondition().ReplaceSpace();
            if (Regex.IsMatch(source, "^[\\d\\.]+[<>=]+v[<>=]+[\\d\\.]+$"))
                return source.Replace("v", "v&&v");
            if (Regex.IsMatch(source, "^[\\d\\.]+-[\\d\\.]+$"))
                return source.Replace("-", "<=v&&v<=");
            return source;
        }

        public static string Joins(this IEnumerable<object> source, string seprator = ",")
        {
            return string.Join(seprator, source);
        }

        /// <summary>
        /// 解析解读条件
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static string AnalizeExplainCondition(this string source)
        {
            if (source.IsNullOrEmpty())
                return source;
            var str = symbols.Keys.Joins("") + "=";
            var match = Regex.Match(source, $"[男女]\\s*[{str}]\\s*（）[a-z]+");
            if (match.Success)
            {
                var value = match.Value;
                value = Regex.Replace(value, "[男女]", "$sex");
                source = Regex.Replace(source, $"◎?[男女]\\s*[{str}]+\\s*（）[a-z]+或◎?[男女]\\s*[{str}]*（）\\s*[a-z]+", value);
            }
            source = source.Replace("（）", "（$v）");
            return source;
        }
    }
}
